In the above example, Field 1 is the only assigned level break.
If both specifications indicated above are given the same line numbers, the sub-totals will occur as follows:
Field 1 field 2 field 3
field 2 field 3
field 2 field
---------- -----------
(All field 2's sub-total 1 sub-total 1 (All field 3's
for each field 1) for each field 1)
Field 1 field 2 field 3
field 2 field 3
field 2 field 3
----------- -----------
sub-total 1 sub-total 1
If both specifications indicated above are given different line numbers, the sub-totals will occur as follows:
Field 1 field 2
field 2
field 2
----------- -----------
subtotal 1 0
(field 2 totals)
field 3
field 3
field 3
---------- ----------
0 subtotal 1
(field 3 totals)
* Section *
18.4 One-to-Many Keyed Read Chain
A one-to-many keyed read chain look-up is a look-up which uses two consecutive fields as lookup fields to locate the fields for the report.
- The first field is in the first file.
- The second and third fields are both in the second file being looked up.
- The first field points to the first record in the second file. The second field points to the next and subsequent records in the second file. The third field is printed for each record from the second file read, until the second field contains a blank, which ends the chain.
Following is the format for a one-to-many keyed read chain look-up specification.
Field matching Field which also Field to be
key field in +> points to next record .key[n] +> included in .key[n]
The above example uses the field named First Inv No in the customer file to locate the first record in the invoice file, by matching with the invoice no (key of INVOICE.DAT). The invoice amount for that record is then included in the report.
The program finds the next record to read by using the next inv no field of current record to locate the next invoice for that customer. Then the amount for that invoice is printed. This continues until the field named next inv no says 0.
This ends the chain for that customer. The program then goes back to the customer file and reads the next customer record, thus starting a new chain.
The only field that would print for the above example specification would be the invoice amt.
* Section *
18.5 Direct Read Look-up by Record Number
The direct read look-up is done by record number, not by key. The record number of the look-up specification is matched with the record number of the looked-up file.
There are two ways to use this type of look-up, as follows:
Using a Literal indicating the desired record number; or
Using a defined field which contains only the record number of the record.
* Section *
18.5.1 Record Number as a Literal
This is the format for the direct read look-up using the record number as a literal. This type of look-up will give you only one record per specification.
"n" Name of field to include
(Replace n with => in report (prints field from
desired record number) indicated record number)
The number of record is on the left side of the arrow. It matches the record number of the field you want to print in the looked-up file.
Example: "4" => customer phone no
The right side of the symbol can contain the name of any field in the file that you want to include in your report. In the above example, this would be the fourth customer phone number in the file.
This is a useful type of look-up when creating form letters. Each line of the letter indicates the next record. Only the field named letter line prints.
Design Specs LETTER FILE
____________________________________
| |
"1" => letter line | Thank you for your interest |
"2" => letter line | in our software program. |
"3" => letter line | Enclosed is an information |
"4" => letter line | package. |
"5" => letter line | |
"6" => letter line | Please call me after you have |
"7" => letter line | reviewed the package to place |
"8" => letter line | your order. |
"9" => letter line | |
"10" => letter line | Sincerely, |
"11" => letter line | |
"12" => letter line | |
"13" => letter line | |
"14" => letter line | Larry True |
"15" => letter line | Sales Representative |
|____________________________________|
* Section *
18.5.2 Direct Read Look-up by Record Number as a Field
This type of direct read look-up can be done if the record numbers for your files correspond, and the files have a one-to-one relationship. If there is no key field between the two files, they can be accessed through the direct read look-up. However, in order to perform this type of look-up, one field would have to contain the record number (or you can create a file with the record number field).
This type of look-up is specified as follows:
Name of field Name of field you
which contains the => want to include
record number in your report
The records of the file on the left side of the look-up symbol correspond to the looked-up file's records. For example, the fourth record in File 1 would have information relating to the fourth record of File 2. If there is no key field between the two files, in this instance they could be accessed through the direct read look-up. However, in order to perform this type of look-up, one field would have to contain the record number.
The preceding specification would give you the customer telephone numbers on your report.
If necessary, you can create a one-field file which contains the record numbers from one through the total number of records which you have in your file. Then that file would be your report sequence file and would look up the other two files to merge the data.
A one-to-many direct read chain is similar to a one-to-many keyed read, except that a record number field is used for looking up.
The first field (record number) is in the first file. The second and third fields are both in the second file being looked up. The first field points to the first record desired from the second file. The second field points to the next and subsequent records in the second file. The third field is printed for each record from the second file read, until the second field contains a zero, which ends the chain.
The above example uses the field named first rec no to match with record number of the looked up file. Then the amount field is included in the report. The next rec no field of that record contains the next record number for that account.
The program locates that record number and includes the amount field from that record in the report. This chain is continued until the next rec no field says 0, which means there are no more records for that account. The program then returns to the first file to locate the next first rec no field (new account).
The above sample lookup specification would give you only the amounts. Other fields to be included in the report must be specified separately.
* Section *
18.7 More on Lookups
Here is some more information on lookups.
* Section *
18.7.1 Accessing Additional Fields
For all of the above keyed read look-ups, there is an additional capability. After one field has been looked up in a file, subsequent fields can be referenced from that file without need for another lookup. Continue to select fields as needed and then select Done with Second File.
* Section *
18.7.2 Sorting on One-to-Many Look-ups
On one-to-many look-ups, you can add to the size of the key (when defining the looked-up file at File Definitions) to "sort" the records on the look-up.
In the example, the key field may be an account number, size of key 6. The next field in record may be the invoice number, size of key 5.
In order to sort by the invoice number (within each account number) when performing the one-to-many look-up, you can specify the size of key as 11, to include both fields. As a one-to-many look-up creates a level break automatically, this will allow for the sort after the level break. Thus, in the above example, the invoice numbers for each account would be sorted.
* Section *
18.7.3 Temporary Index Files
When a look-up specification is performed, the program initially figures out which files need to be looked up. It then builds a temporary index for each of those files using the information about the key which is specified in the File Definitions. Therefore, you need some work space on the disk in order to perform the look-up. The size of the temporary index is computed as follows:
Number of Records * (size of key + 4)
This is usually not a large file. The temporary index is built quite fast and allows for very fast look-ups once built. Since Report Writer uses its own temporary index for each file, there is a lot of flexibility available as to the definitions of keys and the sort order of files. This usually means you can access your data files in ways which cannot normally be done with other programs.
While the report with the look-up specification is printing, this temporary index is read, using the field to the left of the -> to locate the correct record.
This temporary index is normally removed after you run a report. However, you can choose to have this index file kept permanent by indicating Y next to Keep Index File Permanent at File Definition off the Definitions Menu. Then the index file will be rebuilt only if the time/date on the data file is later than the time/date on the index file. This can save time spent by the program rebuilding the index each time a report is run using the indicated lookup specification, but it will take up additional disk space. If you indicate N to Keep Index File Permanent?, the index will be deleted after each report is run.
* Section *
18.7.4 Redefining Fields for Lookups
It may be necessary to define a field twice, changing the Field Type in order to perform a look-up on that field. Both key fields must match exactly, both in size and data type. Otherwise the program won't work.
As an example, numeric fields are translated to alphabetic characters by Report Writer. If you need a binary number to compare to a binary number (eg. COBOL Comp-3 to Comp-3), define the first field (look-up field) as alphabetic to bypass the internal conversion process. See the examples:
Sometimes it is necessary to define a field twice in order to achieve the above. Just define two fields, with different names, that point to the same location in a record. For example, cust no could be redefined as lookup cust no and used solely for lookups if cust no is a Comp-3 field.
* Section *
18.7.5 Performing Lookups to Files with Native Indices
When you perform a lookup from one file to another, Report Writer must first sort the second file by the key field specified in by the lookup command. This implies that Report Writer must build an index for that file. If the key field specified by the lookup has a native index, then it is possible to tell the program to use the native index for performing the lookup. The general layout of the lookup command is the same. To perform a lookup into a file with a native index, use the following format:
For a 1 to 1 lookup use:
file1 field ->> file2 field.key[n]
For a 1 to Many lookup use:
file1 field +>> file2 field.key[n]
Note that the lookup operators -> and +> are followed by an additional >. This second occurrence of > tells Report Writer that this lookup is a Native Index Lookup and will use the native index instead of building its own index. To specify which native key number is to be used, include the .key[n] extension for alternate keys. If the lookup will use the primary key in the second file you may omit this extension as the default is to use key number 1.
Example:
You wish to see a list of customers and which salesman works their account. You will need the following lookup between the customer file and the salesman file:
customer salesman number to salesman's name
specified as: NO SLS CUST->>NAM SLS
Since the salesman's number is the primary key in the looked up file, you do not need the .KEY[n] extension.
Note that it is not necessary to use the enter key field locations at the File Definition off the Dictionary Menu when using Native Index Lookups. In fact these entries should be reserved for key definitions for normal lookups.
* Section *
18.8 Look-ups Summary
- A keyed read look-up is done by matching a field (lookup field) of the primary data file used for the report with the key field of the looked up file. The needed field can then be located.
- A direct read look-up can be done by record number or by a record number field which contains the record number, if your files correspond record by record.
- Once a file is looked up, you can access any field in that file without the need for an additional look-up specification.
- You can save time spent by the program in performing the look-up if you select to keep the index file permanent when defining that file.
- On one-to-many look-ups, you can sort your looked up field by stating the size of key to include the next field.
- Concatenated keys can be used if the file's key contains more than one field.
- A one-to-many lookup automatically creates its own level break. This counts as a level break for calculation purposes.
- Your data files are not altered. Report Writer makes copies of your data, and never changes original data files.
- You can sort by a looked-up field, except on one-to-many look-ups. If you do need to sort by a looked-up field, you may be able to perform a different type of look-up (e.g. one-to-many or many-to-one). Or, you can run a two-step Query process (writing the first report to disk, defining that file and then accessing it again).
- You can use either the primary key or any of five alternate keys to perform a keyed read lookup.
(January 1994).iso/Snow/US English/Atlantis/Atlantis™ Data-68K/Atlantis™ Data Publisher/Atlantis™ Data Publisher.rsrc/icl4_128.png){kind=link}